package com.zhiyou100.javaweb.myservlet.homework.day004.studentmanagementsystem;

import com.zhiyou100.javaweb.jdbc.learn.JavaDataBaseConnectivityUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Types;
import java.util.ArrayList;

/**
 * @packageName: javase_26
 * @className: StudentDao
 * @Description: TODO 学生实体类的JDBC工具类 查询一个、查询所有、添加一个、删除一个、修改一个
 * @author: yang
 * @date: 2020/5/24
 */
public class StudentDao {

    /**
     * @Description: TODO 根据参数Student，修改一个
     * @name: alterOne
     * @param: [intoStudent]
     * @return: boolean
     * @date: 2020/5/25 6:41 下午
     * @auther: yang
     */

    public boolean alterOne(Student intoStudent) {
        try {
            // 1.获取参数
            Integer studentId = intoStudent.getStudentId();
            String studentName = intoStudent.getStudentName();
            String studentGender = intoStudent.getStudentGender();
            Double studentScore = intoStudent.getStudentScore();
            // 2.获取连接
            Connection connection = JavaDataBaseConnectivityUtil.getConnection();
            // 3.准备sql
            String sql = "update student set studentName=?,studentGender=?,studentScore=? where studentId=?";
            // update student set studentName=?,studentGender=?,studentScore=? where studentId=?;
            // 4.获取预编译对象
            PreparedStatement statement = connection.prepareStatement(sql);
            // 5.设置参数
            statement.setString(1, studentName);
            statement.setString(2, studentGender);
            statement.setDouble(3, studentScore);
            statement.setInt(4, studentId);
            // 6.执行sql语句
            int i = statement.executeUpdate();
            // 7. 关闭连接
            JavaDataBaseConnectivityUtil.close(null, statement, connection);
            return i >= 1;

        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * @Description: TODO 根据插入的id，删除一个
     * @name: delOne
     * @param: [intoId]
     * @return: boolean
     * @date: 2020/5/25 6:36 下午
     * @auther: yang
     */

    public boolean delOne(int intoId) {
        try {
            Connection connection = JavaDataBaseConnectivityUtil.getConnection();
            // 获取连接
            String sql = "delete from student where studentId=?";
            // delete from student where studentId=10;
            PreparedStatement statement = connection.prepareStatement(sql);
            // 获取预编译对象
            statement.setInt(1, intoId);
            // 设置参数
            int i = statement.executeUpdate();
            JavaDataBaseConnectivityUtil.close(null, statement, connection);
            // 关闭连接
            return i == 1;

        } catch (Exception e) {
            throw new RuntimeException(e);
        }

    }

    /**
     * @Description: TODO 获取所有的学生
     * @name: getAll
     * @param: []
     * @return: java.util.ArrayList<com.zhiyou100.javaweb.myservlet.homework.day004.studentmanagementsystem.Student>
     * @date: 2020/5/24 12:54 下午
     * @auther: yang
     */

    public ArrayList<Student> getAll() {
        try {
            ArrayList<Student> student = new ArrayList<Student>();
            // 创建容器
            Connection connection = JavaDataBaseConnectivityUtil.getConnection();
            String sql = "select * from student";
            // select * from student;
            PreparedStatement statement = connection.prepareStatement(sql);
            // 获取预编译对象
            ResultSet resultSet = statement.executeQuery();
            // 获取结果集
            while (resultSet.next()) {
                // 处理结果集
                int studentId = resultSet.getInt("studentId");
                String studentName = resultSet.getString("studentName");
                String studentGender = resultSet.getString("studentGender");
                double studentScore = resultSet.getDouble("studentScore");
                Student studentIn = new Student(studentId, studentName, studentGender, studentScore);
                student.add(studentIn);
                // 添加到集合
            }
            JavaDataBaseConnectivityUtil.close(resultSet, statement, connection);
            // 关闭连接
            return student;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }


    /**
     * @Description: TODO 根据id查询一个学生是否存在
     * @name: queryOne
     * @param: [intoId]
     * @return: com.zhiyou100.javaweb.myservlet.homework.day004.studentmanagementsystem.Student
     * @date: 2020/5/25 6:19 下午
     * @auther: yang
     */

    public boolean queryOne(int intoId) {
        try {
            Connection connection = JavaDataBaseConnectivityUtil.getConnection();
            // 获取连接
            String sql = "select * from student where studentId=?";
            // select * from student where studentId=10;
            PreparedStatement statement = connection.prepareStatement(sql);
            // 获取预编译对象
            statement.setInt(1, intoId);
            // 设置参数
            ResultSet resultSet = statement.executeQuery();
            // 获取结果集
            boolean next = resultSet.next();
            JavaDataBaseConnectivityUtil.close(resultSet, statement, connection);
            // 关闭连接
            return next;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * @Description: TODO 根据id获取一个
     * @name: getOne
     * @param: [intoId]
     * @return: com.zhiyou100.javaweb.myservlet.homework.day004.studentmanagementsystem.Student
     * @date: 2020/5/25 6:27 下午
     * @auther: yang
     */

    public Student getOne(int intoId) {

        try {
            Student student = new Student();
            // 放置查询结果
            Connection connection = JavaDataBaseConnectivityUtil.getConnection();
            // 获取连接
            String sql = "select * from student where studentId=?";
            // select * from student where studentId=10;
            PreparedStatement statement = connection.prepareStatement(sql);
            // 获取预编译对象
            statement.setInt(1, intoId);
            // 设置参数
            ResultSet resultSet = statement.executeQuery();
            // 获取结果集
            while (resultSet.next()) {
                student = new Student(resultSet.getInt(1), resultSet.getString(2), resultSet.getString(3), resultSet.getDouble(4));
            }
            JavaDataBaseConnectivityUtil.close(resultSet, statement, connection);
            // 关闭连接
            return student;

        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }


    /**
     * @Description: TODO 添加一个学生
     * @name: addOneStudent
     * @param: [intoStudent]
     * @return: boolean
     * @date: 2020/5/25 5:39 下午
     * @auther: yang
     */
    public boolean addOneStudent(Student intoStudent) {
        try {
            if (!reasonableJustification(intoStudent)) {
                // 传入参数合理性判断
                System.out.println("传入的参数不合理，插入失败");
                return false;
            }
            // 1 获取参数
            Integer studentId = intoStudent.getStudentId();
            String studentName = intoStudent.getStudentName();
            String studentGender = intoStudent.getStudentGender();
            Double studentScore = intoStudent.getStudentScore();

            // 2 获取连接
            Connection connection = JavaDataBaseConnectivityUtil.getConnection();
            // 4 准备sql
            String sql = "insert into student (studentId, studentName, studentGender, studentScore) values (?,?,?,?);";
            // insert into student (studentId, studentName, studentGender, studentScore) values ();
            // 5 获取预编译对象
            PreparedStatement statement = connection.prepareStatement(sql);
            // 6 设置参数
            if (studentId != null) {
                statement.setInt(1, studentId);
            } else {
                statement.setNull(1, Types.NULL);
            }
            statement.setString(2, studentName);
            statement.setString(3, studentGender);
            statement.setDouble(4, studentScore);
            // 7 执行
            int i = statement.executeUpdate();
            // 8 关闭连接
            JavaDataBaseConnectivityUtil.close(null, statement, connection);
            // 9 返回是否添加成功
            return i != 0;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * @Description: TODO 判断student是否合理
     * @name: reasonableJustification
     * @param: [intoStudent]
     * @return: boolean
     * @date: 2020/5/25 6:02 下午
     * @auther: yang
     */

    public boolean reasonableJustification(Student intoStudent) {
        // 1 获取参数
        Integer studentId = intoStudent.getStudentId();
        String studentName = intoStudent.getStudentName();
        String studentGender = intoStudent.getStudentGender();
        Double studentScore = intoStudent.getStudentScore();
        if (studentId != null) {
            if (studentId < 0) {
                return false;
            }
        }
        int maxNameLength = 11;
        if (studentName == null || studentName.length() <= 0 || studentName.length() > maxNameLength) {
            return false;
        }
        if (studentGender.length() > 1) {
            return false;
        }
        double maxScore = 900;
        if (studentScore < 0 || studentScore >= maxScore) {
            return false;
        }
        return studentGender.charAt(0) == '男' || studentGender.charAt(0) == '女';
    }
}
